import sqlite3
from datetime import datetime
from typing import List, Dict, Optional


class UserReviewDB:
    def __init__(self, db_name: str = 'user_reviews.db'):
        """初始化数据库连接"""
        self.db_name = db_name
        self._create_table()

    def _get_connection(self) -> sqlite3.Connection:
        """获取数据库连接，设置行工厂为字典形式"""
        conn = sqlite3.connect(self.db_name)
        conn.row_factory = sqlite3.Row  # 使用Row工厂，可以通过列名访问数据
        return conn

    def _create_table(self) -> None:
        """创建用户评价表"""
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS user_reviews (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            carid text not null ,
            user_nickname TEXT NOT NULL,
            pickup_date TEXT,  -- 提车时间，存储为字符串
            purchase_location TEXT,  -- 购买地点
            naked_car_price TEXT,  -- 裸车价格
            fuel_consumption TEXT,  -- 油耗
            car_model TEXT,  -- 车型
            rating INTEGER,  -- 评分
            review_time TEXT,  -- 评论时间
            review_content TEXT  -- 评论内容
        )
        """

        with self._get_connection() as conn:
            conn.execute(create_table_sql)

    def insert_review(self, review_data: Dict[str, object]) -> int:
        """
        插入一条用户评价
        :param review_data: 包含评价信息的字典
        :return: 插入行的ID
        """
        # 设置默认评论时间为当前时间
        if 'review_time' not in review_data:
            review_data['review_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        insert_sql = """
        INSERT INTO user_reviews (
           carid,
            user_nickname, pickup_date, purchase_location, 
            naked_car_price, fuel_consumption, car_model, 
            rating, review_time, review_content
        ) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?)
        """

        params = (
            review_data['carid'],
            review_data['user_nickname'],
            review_data.get('pickup_date'),
            review_data.get('purchase_location'),
            review_data.get('naked_car_price'),
            review_data.get('fuel_consumption'),
            review_data.get('car_model'),
            review_data.get('rating'),
            review_data['review_time'],
            review_data.get('review_content')
        )

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(insert_sql, params)
            conn.commit()
            return cursor.lastrowid

    def get_review_by_id(self, review_id: int) -> Optional[Dict[str, object]]:
        """根据ID获取评价"""
        query_sql = "SELECT * FROM user_reviews WHERE id = ?"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (review_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_reviews_by_carid(self, carid: str) -> List[Dict[str, object]]:
        """获取所有评价"""
        query_sql = "SELECT * FROM user_reviews where carid = ? ORDER BY review_time DESC"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (carid,))
            return [dict(row) for row in cursor.fetchall()]

    def get_reviews_by_rating(self, min_rating: int) -> List[Dict[str, object]]:
        """根据最低评分获取评价"""
        query_sql = "SELECT * FROM user_reviews WHERE rating >= ? ORDER BY rating DESC"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (min_rating,))
            return [dict(row) for row in cursor.fetchall()]

    def get_reviews_by_car_model(self, car_model: str) -> List[Dict[str, object]]:
        """根据车型获取评价"""
        query_sql = "SELECT * FROM user_reviews WHERE car_model LIKE ? ORDER BY review_time DESC"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (f'%{car_model}%',))
            return [dict(row) for row in cursor.fetchall()]

    def get_average_rating(self) -> float:
        """获取平均评分"""
        query_sql = "SELECT AVG(rating) as avg_rating FROM user_reviews"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql)
            result = cursor.fetchone()
            return result['avg_rating'] if result['avg_rating'] is not None else 0.0

    def close(self):
        """关闭数据库连接"""
        if hasattr(self, '_conn') and self._conn:
            self._conn.close()


import sqlite3
from typing import List, Dict, Optional


class CommentAnalysisDB:
    def __init__(self, db_name: str = 'user_reviews.db'):
        """
        初始化数据库连接并创建表

        :param db_name: 数据库文件名
        """
        self.db_name = db_name
        self._create_table()

    def _get_connection(self) -> sqlite3.Connection:
        """
        获取数据库连接，设置行工厂为字典形式

        :return: SQLite 连接对象
        """
        conn = sqlite3.connect(self.db_name)
        conn.row_factory = sqlite3.Row  # 使用Row工厂，可以通过列名访问数据
        return conn

    def _create_table(self) -> None:
        """
        创建评论优缺点分析表
        """
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS comment_analysis (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            commit_id TEXT NOT NULL UNIQUE,  -- 原始评论ID，确保不重复
            advantage TEXT,                   -- 优点分析结果
            disadvantage TEXT,                -- 缺点分析结果
            create_time TEXT DEFAULT (datetime('now', 'localtime'))  -- 创建时间
        )
        """

        with self._get_connection() as conn:
            conn.execute(create_table_sql)

    def insert_analysis(self, commit_id: str, advantage: str = None, disadvantage: str = None) -> int:
        """
        插入一条评论分析结果

        :param commit_id: 原始评论ID
        :param advantage: 优点分析结果
        :param disadvantage: 缺点分析结果
        :return: 插入行的ID
        """
        insert_sql = """
        INSERT INTO comment_analysis (commit_id, advantage, disadvantage)
        VALUES (?, ?, ?)
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(insert_sql, (commit_id, advantage, disadvantage))
            conn.commit()
            return cursor.lastrowid

    def batch_insert_analysis(self, analyses: List[Dict[str, str]]) -> int:
        """
        批量插入评论分析结果

        :param analyses: 分析结果列表，每个元素应包含commit_id、advantage和disadvantage
        :return: 插入的行数
        """
        insert_sql = """
        INSERT INTO comment_analysis (commit_id, advantage, disadvantage)
        VALUES (?, ?, ?)
        """

        data = [(a['commit_id'], a.get('advantage'), a.get('disadvantage')) for a in analyses]

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.executemany(insert_sql, data)
            conn.commit()
            return cursor.rowcount

    def get_analysis_by_id(self, analysis_id: int) -> Optional[Dict[str, str]]:
        """
        根据ID获取分析结果

        :param analysis_id: 分析记录ID
        :return: 分析结果字典或None
        """
        query_sql = "SELECT * FROM comment_analysis WHERE id = ?"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (analysis_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_analysis_by_commit_id(self, commit_id: str) -> Optional[Dict[str, str]]:
        """
        根据原始评论ID获取分析结果

        :param commit_id: 原始评论ID
        :return: 分析结果字典或None
        """
        query_sql = "SELECT * FROM comment_analysis WHERE commit_id = ?"

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (commit_id,))
            row = cursor.fetchone()
            return dict(row) if row else None

    def get_by_carid(self,carid) -> List[Dict[str, str]]:
        """
        获取所有缺点分析结果（过滤掉空值或过短的缺点）

        :param min_length: 缺点文本的最小长度要求
        :return: 缺点分析结果列表
        """
        query_sql = """
        SELECT ca.*
        FROM comment_analysis ca left join user_reviews ur on ca.commit_id = ur.id where ur.carid = ?
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (carid,))
            return [dict(row) for row in cursor.fetchall()]

    def get_all_advantages(self, min_length: int = 5) -> List[Dict[str, str]]:
        """
        获取所有优点分析结果（过滤掉空值或过短的优点）

        :param min_length: 优点文本的最小长度要求
        :return: 优点分析结果列表
        """
        query_sql = """
        SELECT id, commit_id, advantage 
        FROM comment_analysis 
        WHERE length(trim(advantage)) >= ?
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (min_length,))
            return [dict(row) for row in cursor.fetchall()]

    def get_all_disadvantages(self, min_length: int = 5) -> List[Dict[str, str]]:
        """
        获取所有缺点分析结果（过滤掉空值或过短的缺点）

        :param min_length: 缺点文本的最小长度要求
        :return: 缺点分析结果列表
        """
        query_sql = """
        SELECT id, commit_id, disadvantage 
        FROM comment_analysis 
        WHERE length(trim(disadvantage)) >= ?
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (min_length,))
            return [dict(row) for row in cursor.fetchall()]

    def get_most_common_advantages(self, top_n: int = 10) -> List[Dict[str, str]]:
        """
        获取最常见的优点（按出现频率排序）

        :param top_n: 返回前N个结果
        :return: 优点统计结果列表
        """
        query_sql = """
        SELECT advantage, count(*) as frequency 
        FROM comment_analysis 
        WHERE advantage IS NOT NULL AND trim(advantage) != ''
        GROUP BY advantage 
        ORDER BY frequency DESC 
        LIMIT ?
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (top_n,))
            return [dict(row) for row in cursor.fetchall()]

    def get_most_common_disadvantages(self, top_n: int = 10) -> List[Dict[str, str]]:
        """
        获取最常见的缺点（按出现频率排序）

        :param top_n: 返回前N个结果
        :return: 缺点统计结果列表
        """
        query_sql = """
        SELECT disadvantage, count(*) as frequency 
        FROM comment_analysis 
        WHERE disadvantage IS NOT NULL AND trim(disadvantage) != ''
        GROUP BY disadvantage 
        ORDER BY frequency DESC 
        LIMIT ?
        """

        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query_sql, (top_n,))
            return [dict(row) for row in cursor.fetchall()]

    def close(self):
        """关闭数据库连接"""
        if hasattr(self, '_conn') and self._conn:
            self._conn.close()



# 使用示例
if __name__ == '__main__':

    ######################################################UserReviewDB######################################################

    # 创建数据库工具实例
    db = UserReviewDB()

    # 插入示例数据
    review1 = {
        'carid':'565',
        'user_nickname': '汽车爱好者',
        'pickup_date': '2023-05-15',
        'purchase_location': '北京朝阳4S店',
        'naked_car_price': 185000.0,
        'fuel_consumption': 7.5,
        'car_model': 'Toyota Camry',
        'rating': 5,
        'review_content': '非常满意的购车体验，油耗低，空间大。'
    }

    review2 = {
        'carid': '565',
        'user_nickname': '速度与激情',
        'pickup_date': '2023-06-20',
        'purchase_location': '上海浦东4S店',
        'naked_car_price': 220000.0,
        'fuel_consumption': 8.2,
        'car_model': 'Honda Accord',
        'rating': 4,
        'review_content': '动力强劲，内饰豪华，油耗略高。'
    }

    db.insert_review(review1)
    db.insert_review(review2)

    # 查询所有评价
    print("所有评价:")
    for review in db.get_reviews_by_carid('535'):
        print(review)

    # 查询评分4分以上的评价
    print("\n评分4分以上的评价:")
    for review in db.get_reviews_by_rating(4):
        print(review)

    # 查询特定车型的评价
    print("\nToyota Camry的评价:")
    for review in db.get_reviews_by_car_model('Camry'):
        print(review)

    # 查询平均评分
    print(f"\n平均评分: {db.get_average_rating():.1f}")

######################################################CommentAnalysisDB######################################################
  # 创建数据库工具实例
    db = CommentAnalysisDB()

    # 插入单条分析结果
    db.insert_analysis(
        commit_id="comment_001",
        advantage="空间大，油耗低",
        disadvantage="隔音效果一般"
    )

    # 批量插入分析结果
    analyses = [
        {"commit_id": "comment_002", "advantage": "动力强劲", "disadvantage": "悬挂偏硬"},
        {"commit_id": "comment_003", "advantage": "配置丰富", "disadvantage": "车机反应慢"},
        {"commit_id": "comment_004", "advantage": "外观漂亮", "disadvantage": "后备箱空间小"}
    ]
    db.batch_insert_analysis(analyses)

    # 查询示例
    print("所有优点分析：")
    for advantage in db.get_all_advantages():
        print(advantage)

    print("\n最常见的3个缺点：")
    for disadvantage in db.get_most_common_disadvantages(top_n=3):
        print(f"{disadvantage['disadvantage']} (出现{disadvantage['frequency']}次)")